﻿Imports Microsoft.VisualBasic
Imports System.Data.SqlClient
Imports System.Net.Mail
Imports System.Data

Public Class functions
    Public Shared Function addcomments(ByVal a As String) As Integer
        Try
            Dim cn As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename='|DataDirectory|\Database.mdf';Integrated Security=True;User Instance=True")
            cn.Open()
            Dim cmd As New SqlCommand(a + "')", cn)
            Return (cmd.ExecuteNonQuery())
            cn.Close()
        Catch ex As Exception

        End Try
    End Function

    Public Shared Function addcategory(ByVal a As String) As Integer
        Try
            Dim cn As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename='|DataDirectory|\Database.mdf';Integrated Security=True;User Instance=True")
            Dim x As Integer
            Dim cmd As New SqlCommand(a + "')", cn)
            Try
                cn.Open()
                If (cmd.ExecuteNonQuery = 1) Then
                    x = 1
                    Return (x)
                Else
                    x = 0
                    Return (x)
                End If
                cn.Close()
            Catch
            End Try

        Catch ex As Exception

        End Try
    End Function

    Public Shared Function delcomment(ByVal A As String) As Integer
        Dim cn As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename='|DataDirectory|\Database.mdf';Integrated Security=True;User Instance=True")
        cn.Open()
        Dim cmd As New SqlCommand("delete from comment where commentid=" + A, cn)
        Dim i As Integer = cmd.ExecuteNonQuery()
        cn.Close()

    End Function

    Public Shared Function deletepicture(ByVal a As String) As String
        Dim cn As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename='|DataDirectory|\Database.mdf';Integrated Security=True;User Instance=True")

        cn.Open()
        Dim cmd As New SqlCommand("delete from comment where imageid=" + a, cn)
        cmd.ExecuteNonQuery()
        cn.Close()

        cn.Open()
        cmd = New SqlCommand("select imageurl from imagetable where imageid=" + a, cn)
        Dim delfilename As String = cmd.ExecuteScalar
        cn.Close()

        cn.Open()
        cmd = New SqlCommand("delete from imagetable where imageid=" + a, cn)
        cmd.ExecuteNonQuery()
        cn.Close()
        Return (delfilename)


    End Function

    Public Shared Function deluser(ByVal uname As String, ByVal status As String) As Integer
        Dim cn As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename='|DataDirectory|\Database.mdf';Integrated Security=True;User Instance=True")
        cn.Open()
        If status = "allowed" Then
            Dim cmd As New SqlCommand("update users set banned='banned' where username='" + uname + "'", cn)
            Return (cmd.ExecuteNonQuery())
        Else
            Dim cmd As New SqlCommand("update users set banned='allowed' where username='" + uname + "'", cn)
            Return (cmd.ExecuteNonQuery())
        End If
        cn.Close()

    End Function


    Public Shared Function addratting(ByVal ids As String) As Integer
        Dim cn As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename='|DataDirectory|\Database.mdf';Integrated Security=True;User Instance=True")
        cn.Open()
        Dim cmd As New SqlCommand("update imagetable set likes=likes+1 where imageid=" + ids, cn)
        cmd.ExecuteNonQuery()
        cn.Close()
    End Function

    Public Shared Function negratting(ByVal ids As String) As Integer
        Dim cn As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename='|DataDirectory|\Database.mdf';Integrated Security=True;User Instance=True")
        cn.Open()
        Dim cmd As New SqlCommand("update imagetable set dislikes=dislikes+1 where imageid=" + ids, cn)
        cmd.ExecuteNonQuery()
        cn.Close()

    End Function
    


    Public Shared Function forgotpassword(ByVal username As String, ByVal userans As String) As Integer
        Dim cn As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename='|DataDirectory|\Database.mdf';Integrated Security=True;User Instance=True")
        Dim cmd As New SqlCommand("select answer from users where username='" + username + "'", cn)
        cn.Open()
        Dim dr As SqlDataReader = cmd.ExecuteReader
        dr.Read()
        Dim ans As String = dr("answer")
        cn.Close()

        If (ans.ToLower = userans) Then
            Return (1)
        Else
            Return (0)
        End If


    End Function


    Public Shared Function sendmail(ByVal a As String, ByVal txtEmail As String) As String
        Dim cn As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename='|DataDirectory|\Database.mdf';Integrated Security=True;User Instance=True")

        Dim cmd As New SqlCommand("select username,FIRSTNAME,password,emailid from users where username='" + a + "'", cn)
        Dim dr As SqlDataReader
        Try
            cn.Open()
            dr = cmd.ExecuteReader
            dr.Read()
        Catch
        End Try

        Dim uname As String = dr("username")
        Dim password As String = dr("password")
        Dim email As String = dr("emailid")
        Dim FIRSTNAME As String = dr("FIRSTNAME")

        cn.Close()


        If txtEmail.ToLower = email.ToLower Then
            ' here i am sending mail to user
            Const googleMailPortNumber As Integer = 587
            Const userName As String = "rishi.patel007"
            Const pwd As String = "rishipat"
            Dim Mail As MailMessage
            Dim smtp As SmtpClient

            Dim from As String = "rishi.patel007@gmail.com"
            Dim too As String = email
            Dim subject As String = "Password Recovery"
            Dim body As String

            body = "<b><p class='MsoNormal'>Hi " + FIRSTNAME + "<span style='mso-spacerun:yes;'>&nbsp;</span>,</p>"

            body += "<p class='MsoNormal'>This is the email notification for your inquiry about forgot"
            body += "password.</p>"
            body += "<p class='MsoNormal'><o:p>&nbsp;</o:p></p>"
            body += "<p class='MsoNormal'>Username : </p><B>" + uname + "<B>"
            body += "<p class='MsoNormal'>Password : </p><B>" + password + "<B>"
            body += "<p class='MsoNormal'><o:p>&nbsp;</o:p></p>"
            body += "<p class='MsoNormal'>Please, Don’t share your private information (such as password"
            body += ")to others.</p>"
            body += "<p class='MsoNormal'><o:p>&nbsp;</o:p></p>"
            body += "<p class='MsoNormal'>Thank you,</p>"
            body += "<p class='MsoNormal'><a href='http://www.onlinecricket.com'>http://www.onlinecricket.com</a></p>"
            body += "<p class='MsoNormal'><o:p>&nbsp;</o:p></p>"
            body += "<p class='MsoNormal'><o:p>&nbsp;</o:p></p></b>"



            Mail = New MailMessage(from, too, subject, body)

            Mail.IsBodyHtml = True
            smtp = New SmtpClient("smtp.gmail.com", googleMailPortNumber)
            smtp.EnableSsl = True
            smtp.DeliveryMethod = SmtpDeliveryMethod.Network
            smtp.Credentials = New System.Net.NetworkCredential(userName, pwd)

            Try
                smtp.Send(Mail)
                Return (1)
            Catch ex As Exception
                Return (2)
            End Try

        Else
            Return (0)

            Exit Function

        End If
    End Function

    Public Shared Function changepassword(ByVal a As String) As Integer
        Try
            Dim cn As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename='|DataDirectory|\Database.mdf';Integrated Security=True;User Instance=True")

            Dim cmd As New SqlCommand(a + "'", cn)
            cn.Open()
            Return (cmd.ExecuteNonQuery())
            cn.Close()
            

        Catch ex As Exception
            MsgBox(ex.Message)

        End Try
    End Function

    Public Shared Function logout(ByVal sqlcmd As String) As Integer
        Dim cn As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename='|DataDirectory|\Database.mdf';Integrated Security=True;User Instance=True")
        Try
            cn.Open()
            Dim cmd As SqlCommand = New SqlCommand(sqlcmd, cn)
            Return (cmd.ExecuteNonQuery())
            cn.Close()
        Catch ex As Exception
            cn.Close()
        End Try



    End Function



    Public Shared Function login(ByVal txtusername As String, ByVal txtpassword As String, ByVal sessionuser As String) As Integer
        Try
            Dim cn As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename='|DataDirectory|\Database.mdf';Integrated Security=True;User Instance=True")
            Dim str As String = "select count(*) from users where username='" + txtusername + "' and password = '" + txtpassword + "'"
            Dim cmd As New SqlCommand(str, cn)
            cn.Open()
            If cmd.ExecuteScalar = 1 Then
                cn.Close()
                cn.Open()
                cmd = New SqlCommand("select banned from users where username='" + txtusername + "'", cn)
                If (cmd.ExecuteScalar = "allowed") Then
                    sessionuser = txtusername
                    If (txtusername = "admin") Then
                        Return (1)
                    End If
                    Return (2)

                Else
                    Return (3)

                End If
            Else
                Return (4)

            End If
        Catch ex As Exception

        End Try
    End Function

    Public Shared Function sendmessage(ByVal str As String) As Integer
        Dim cn As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename='|DataDirectory|\Database.mdf';Integrated Security=True;User Instance=True")
        Dim cmd As New SqlCommand(str, cn)
        cn.Open()

        If cmd.ExecuteNonQuery = 1 Then
            Return (1)
        Else
            Return (0)
        End If

    End Function

    Public Shared Function delmessages(ByVal str As String) As Integer
        Dim cn As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename='|DataDirectory|\Database.mdf';Integrated Security=True;User Instance=True")
        Dim cmd As New SqlCommand(str, cn)
        Try
            cn.Open()
            cmd.ExecuteNonQuery()
            cn.Close()
        Catch ex As Exception

        End Try
    End Function

    Public Shared Function updateprofile(ByVal str As String) As Integer
        Dim cn As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename='|DataDirectory|\Database.mdf';Integrated Security=True;User Instance=True")
        Dim cmd As SqlCommand
        cmd = New SqlCommand(str + "'", cn)
        cn.Open()
        Return (cmd.ExecuteNonQuery())
    End Function


    Public Shared Function check(ByVal str As String) As Integer
        Dim cn As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename='|DataDirectory|\Database.mdf';Integrated Security=True;User Instance=True")
        cn.Open()
        Dim cmd As SqlCommand = New SqlCommand(str, cn)

        If cmd.ExecuteScalar = 1 Then
            ' so it means user exists
            Return (1)
        Else
            Return (0)
        End If
        cn.Close()

    End Function

    Public Shared Function registration(ByVal str As String) As Integer
        Dim cn As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename='|DataDirectory|\Database.mdf';Integrated Security=True;User Instance=True")
        Try
            cn.Open()
            Dim cmd As SqlCommand = New SqlCommand(str, cn)
            Dim i As Integer = cmd.ExecuteNonQuery()
            Return (i)
            cn.Close()
        Catch ex As Exception
            cn.Close()
        End Try
    End Function

    Public Shared Function upload(ByVal str As String) As Integer
        Dim cn As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename='|DataDirectory|\Database.mdf';Integrated Security=True;User Instance=True")
        Try
            Dim cmd As New SqlCommand(str, cn)
            cn.Open()
            If cmd.ExecuteNonQuery = 1 Then
                Return (1)
            End If
            cn.Close()

        Catch ex As Exception

        End Try

    End Function
End Class
